package cn.jly.boot.mp.service.impl;

import cn.jly.boot.mp.bean.User;
import cn.jly.boot.mp.bean.UserInfo;
import cn.jly.boot.mp.repository.UserInfoRepository;
import cn.jly.boot.mp.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Service;

import javax.persistence.criteria.*;
import java.sql.*;
import java.util.List;

/**
 * @author lanyangji
 * @description
 * @date 2020/12/26 下午 8:54
 */
@Slf4j
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    UserInfoRepository userInfoRepository;

    @Override
    public List<User> getAll() {
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                final User user = new User();
                user.setId(resultSet.getLong("id"));
                user.setUsername(resultSet.getString("username"));
                user.setAge(resultSet.getInt("age"));
                user.setEmail(resultSet.getString("email"));
                user.setDeptId(resultSet.getInt("department_id"));
                return user;
            }
        });
    }

    /**
     * 复杂添加
     * 例如在数据插入的过程中希望实现主键回填，那么可以使用PreparedStatementCreator
     *
     * @param user
     * @return
     */
    @CacheEvict(value = "userCache", allEntries = true)
    @Override
    public int add(User user) {
        String sql = "insert into user(username, age, email, department_id) values(?, ?, ?, ?)";
        // 保存生成的id
        final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        final int update = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                final PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1, user.getUsername());
                preparedStatement.setInt(2, user.getAge());
                preparedStatement.setString(3, user.getEmail());
                preparedStatement.setInt(4, user.getDeptId());
                return preparedStatement;
            }
        }, keyHolder);
        // 获取生成的id
        user.setId(ObjectUtils.isEmpty(keyHolder.getKey()) ? null : keyHolder.getKey().longValue());
        log.info("插入数据 -> {}", user);
        return update;
    }

    @CacheEvict(value = "userCache", allEntries = true)
    @Override
    public int deleteById(Long id) {
        String sql = "delete from user where id = ?";
        return jdbcTemplate.update(connection -> {
            final PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            return preparedStatement;
        });
    }

    @CacheEvict(value = "userCache", allEntries = true)
    @Override
    public int updateById(User user) {
        if (ObjectUtils.isEmpty(user.getId())) {
            return 0;
        }
        String sql = "update user set username = ?, email = ?, age = ?, department_id = ? where id = ?";
        return jdbcTemplate.update(connection -> {
            final PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, user.getUsername());
            preparedStatement.setObject(2, user.getEmail());
            preparedStatement.setObject(3, user.getAge());
            preparedStatement.setObject(4, user.getDeptId());
            preparedStatement.setObject(5, user.getId());
            return preparedStatement;
        });
    }

    /**
     * 构造sql
     * select * from user where username like ? or (email like ? and age > ?)
     * <p>
     * 缓存说明
     * 当执行到一个被 @Cacheable 注解的方法时，Spring 首先检查 condition 条件是否满足，如果不满足，执行方法，返回；
     * 如果满足，在缓存空间中查找使用 key 存储的对象，如果找到，将找到的结果返回，
     * 如果没有找到执行方法，将方法的返回值以 key-value 对象的方式存入缓存中，然后方法返回。
     * <p>
     * 需要注意的是当一个支持缓存的方法在对象内部被调用时是不会触发缓存功能的。
     *
     * @param userInfo
     * @param pageable
     * @return
     */
    @Cacheable(value = "userCache", key = "#userInfo.username", condition = "#userInfo.age > 20")
    @Override
    public Page<UserInfo> findByCondition(UserInfo userInfo, Pageable pageable) {
        return userInfoRepository.findAll(new Specification<UserInfo>() {
            @Override
            public Predicate toPredicate(Root<UserInfo> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                final Path<String> username = root.get("username");
                final Path<String> email = root.get("email");
                final Path<Integer> age = root.get("age");

                // 构造sql
                final Predicate predicate = criteriaBuilder.and(
                        criteriaBuilder.like(email, "%" + userInfo.getEmail() + "%"),
                        criteriaBuilder.greaterThan(age, userInfo.getAge())
                );

                return criteriaBuilder.or(predicate, criteriaBuilder.like(username, "%" + userInfo.getUsername() + "%"));
            }
        }, pageable);
    }
}
